/*java jdk 1.7+
# -*- coding: utf-8 -
'''
Created on 2022年6月15日
@author: zxyong 13738196011
'''*/

package com.plugins.usereflect;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Enumeration;

import org.json.JSONArray;
import org.json.JSONObject;

import com.zxy.business.Ope_DB_Cent;
import com.zxy.common.Com_Fun;
import com.zxy.common.Com_Para;
import com.zxy.common.Db_Common_InterFace;
import com.zxy.common.PooledConnection_InterFace;
import com.zxy.db1.Db_Common1;
import com.zxy.model.T_PROC_NAME;

//加载系统库表或视图信息
public class A01_ADDSCH_IN implements com.zxy.common.ParamName_InterFace
{
	public StringBuilder	strResult		= new StringBuilder();
	public String			user_main_id			= null;
	public String			session_id		= null;
	public String			param_name		= "";
	// 数据源ID
	public String			param_value1	= null;
	// 表或视图名
	public String			param_value2	= null;
	// 表或视图 类型0:表 1:视图
	public String			param_value3	= null;
	public String			param_value4	= null;
	public String			param_value5	= null;
	public String			param_value6	= null;
	public String			param_value7	= null;
	public String			param_value8	= null;
	public String			param_value9	= null;
	public String			param_value10	= null;

	public void init_start()
	{
		java.sql.ResultSet rs = null;
		StringBuilder sqlException = null;
		PooledConnection_InterFace pi = null;
		Db_Common_InterFace dbc = null;
		Ope_DB_Cent odc = new Ope_DB_Cent();

		// 判断表库中是否存在
		try
		{
			Object dbcobject = odc.Get_Db_Common(Integer.valueOf(param_value1));
			dbc = (Db_Common_InterFace) (odc.Obj_To_Db_Common(dbcobject));
			rs = dbc.Common_Sql(new StringBuilder("select 1"));
			sqlException = dbc.getSqlException();
			pi = (PooledConnection_InterFace) dbc.getConn_a();
			String DB_EN_NAME = pi.getConnection().getCatalog();
			
			StringBuilder strSql = new StringBuilder();
			if(java.util.Objects.equals(this.param_value3,"0"))
			{
				strSql.append("select TABLE_NAME FROM information_schema.`TABLES` "
						+ "where TABLE_SCHEMA='" + DB_EN_NAME
						+ "' and TABLE_TYPE = 'BASE TABLE' and TABLE_NAME='"+this.param_value2+"'");
			}else if(java.util.Objects.equals(this.param_value3,"1"))
			{
				strSql.append("select TABLE_NAME FROM information_schema.`VIEWS` "
						+ "where TABLE_SCHEMA='" + DB_EN_NAME
						+ "' and TABLE_NAME='"+this.param_value2+"'");
			}
			//判断系统schema中是否存在表或视图名
			JSONArray jsary = odc.SqlToJson(1,strSql.toString());
			if(jsary.length() == 0)
			{
				this.strResult.delete(0,this.strResult.length());
				this.strResult.append(Com_Fun.get_ope_result(param_name,"0","数据加载失败系统库中表名或视图名不存在"));
			}
			if(this.strResult.length() == 0)
			{
				//元数据表中是否存在该数据记录
				strSql.delete(0,strSql.length());
				strSql.append("select MAIN_ID from t_single_table where TABLE_EN_NAME = '"
						+ this.param_value2 + "' and DB_ID = '"
						+ this.param_value1 + "'");
				jsary = odc.SqlToJson(1,strSql.toString());
				if(jsary.length() > 0)
				{
					this.strResult.delete(0,this.strResult.length());
					this.strResult.append(Com_Fun.get_ope_result(param_name,"0","数据加载失败元数据表中已存在该表名或视图名"));
				}
			}

			if(this.strResult.length() == 0)
			{
				//插入元数据表
				Db_Common1 dbc1 = new Db_Common1();
				T_PROC_NAME trn = new T_PROC_NAME();
				trn.INF_TYPE = "2";
				trn.INF_EN_SQL = "insert into t_single_table(TABLE_CN_NAME,TABLE_EN_NAME,CREATE_DATE,S_DESC,TABLE_TYPE,DB_ID,S_SORT) "
						+ " values(?,?,?,?,?,?,?)";
				String[] Parameters =
				{ "", this.param_value2, Com_Fun.DateTimeStr("yyyy-MM-dd HH:mm:ss"), "", this.param_value3,
						this.param_value1, "0" };
				String[] ParamTypes =
				{ "STRING", "STRING", "STRING", "STRING", "INT", "INT", "INT" };
				String[] ParamOutName = new String[0];
				String[] ParamOutType = new String[0];
				ResultSet rs2 = dbc1.Common_Sql_Proc(param_name,Parameters,ParamTypes,ParamOutName,ParamOutType,trn);
				sqlException = dbc1.getSqlException();
				int iMAIN_ID = dbc1.getLastrowid();
				if(sqlException.length() > 0)
				{
					this.strResult.delete(0,this.strResult.length());
					this.strResult.append(Com_Fun.get_ope_result(param_name,"0","数据加载失败,失败原因:" + sqlException.toString()));
				}else if(java.util.Objects.equals(this.param_value3,"0"))
				{
					//插入表字段列
					strSql.delete(0,strSql.length());
					strSql.append("insert into t_table_column(TABLE_ID,COLUMN_CN_NAME,COLUMN_EN_NAME,"
							+"COLUMN_TYPE,COLUMN_LENGTH,COLUMN_QRY,CREATE_DATE,S_SORT) select '" + String.valueOf(iMAIN_ID)
							+ "',COLUMN_COMMENT,UPPER(COLUMN_NAME),"
							+ "case when DATA_TYPE = 'int' then '2' "
							+ "when DATA_TYPE = 'float' then '3' "
							+ "when DATA_TYPE = 'decimal' then '3' "
							+ "when DATA_TYPE = 'double' then '3' "
							+ "when DATA_TYPE = 'date' then '4' "
							+ "when DATA_TYPE = 'datetime' then '4' "
							+ "else '1'end as 'DATA_TYPE',"
							+ "case when CHARACTER_MAXIMUM_LENGTH is null or CHARACTER_MAXIMUM_LENGTH = '' then '10' "
							+ "else CHARACTER_MAXIMUM_LENGTH end as 'COLUMN_LENGTH','0',now(),'0' "
							+ "FROM information_schema.`COLUMNS` where TABLE_SCHEMA=? and TABLE_NAME = ?");					
					trn.INF_EN_SQL = strSql.toString();
					Parameters = new String[] {DB_EN_NAME, this.param_value2};
					ParamTypes = new String[] {"STRING", "STRING"};
					
					rs2 = dbc1.Common_Sql_Proc(param_name,Parameters,ParamTypes,ParamOutName,ParamOutType,trn);
					
					if(sqlException.length() == 0)
						sqlException = dbc.getSqlException();
					if(sqlException.length() > 0)
					{
						this.strResult.delete(0,this.strResult.length());
						this.strResult.append(Com_Fun.get_ope_result(param_name,"0","数据加载失败,失败原因:" + sqlException.toString()));
					}
				}
				//插入视图返回值
				else if(java.util.Objects.equals(this.param_value3,"1")){
					trn.INF_EN_SQL = "select * from "+this.param_value2;
					Parameters = new String[] {};
					ParamTypes = new String[] {};
					rs = dbc.Common_Sql_Proc(param_name,Parameters,ParamTypes,ParamOutName,ParamOutType,trn);
					if(sqlException.length() == 0)
						sqlException = dbc.getSqlException();					
					if(sqlException.length() > 0)
					{
						this.strResult.delete(0,this.strResult.length());
						this.strResult.append(Com_Fun.get_ope_result(param_name,"0","数据加载失败,失败原因:" + sqlException.toString()));
					}
					else{
						// 获取列数
						ResultSetMetaData metaData = rs.getMetaData();
						int columnCount = metaData.getColumnCount();
						// 遍历每一列
						for(int i = 1;i <= columnCount;i++)
						{
							String columnName = metaData.getColumnLabel(i);
							trn.INF_EN_SQL = "insert into t_table_column(TABLE_ID,COLUMN_CN_NAME,COLUMN_EN_NAME,"
							+"COLUMN_TYPE,COLUMN_LENGTH,COLUMN_QRY,CREATE_DATE,S_SORT) values(?,?,?,?,?,?,?,?)";
							Parameters = new String[] {String.valueOf(iMAIN_ID),"",columnName.toUpperCase(),
									"1","500","0",Com_Fun.DateTimeStr("yyyy-MM-dd HH:mm:ss"),"0"};
							ParamTypes = new String[] {"STRING","STRING","STRING","STRING","STRING","STRING","STRING","STRING"};
							rs2 = dbc1.Common_Sql_Proc(param_name,Parameters,ParamTypes,ParamOutName,ParamOutType,trn);
							if(sqlException.length() == 0)
								sqlException = dbc.getSqlException();
						}
					}
				}
				rs2.getStatement().close();
				rs2.close();
				dbc1.conn_a.returnConnection();
			}			
			if(this.strResult.length() == 0 && sqlException.length() == 0){
				this.strResult.delete(0,this.strResult.length());
				this.strResult.append(Com_Fun.get_ope_result(param_name,"1","数据加载成功" + sqlException.toString()));
			}			
		}
		catch(Exception e)
		{
			this.strResult.delete(0,this.strResult.length());
			this.strResult.append(Com_Fun.get_ope_result(param_name,"0","数据加载失败" + e.toString()));
		}
		try
		{
			if(!java.util.Objects.equals(rs,null))
			{
				rs.getStatement().close();
				rs.close();
			}
			if(!java.util.Objects.equals(dbc,null) && !pi.isB_nocursor())
			{
				if(!java.util.Objects.equals(pi,null)
						&& !java.util.Objects.equals(pi.getCalls(),null))
					pi.getCalls().close();
			}else if(!java.util.Objects.equals(dbc,null))
			{
				pi.returnConnection();
			}
		}
		catch(SQLException e)
		{
		}
	}

	/**
	 * @return strResult
	 */
	public StringBuilder getStrResult()
	{
		return strResult;
	}

	/**
	 * @param strResult
	 *            要设置的 strResult
	 */
	public void setStrResult(StringBuilder strResult)
	{
		this.strResult = strResult;
	}

	/**
	 * @return user_main_id
	 */
	public String getUser_main_id()
	{
		return user_main_id;
	}

	/**
	 * @param user_main_id
	 *            要设置的 user_main_id
	 */
	public void setUser_main_id(String user_main_id)
	{
		this.user_main_id = user_main_id;
	}

	/**
	 * @return session_id
	 */
	public String getSession_id()
	{
		return session_id;
	}

	/**
	 * @param session_id
	 *            要设置的 session_id
	 */
	public void setSession_id(String session_id)
	{
		this.session_id = session_id;
	}

	/**
	 * @return param_name
	 */
	public String getParam_name()
	{
		return param_name;
	}

	/**
	 * @param param_name
	 *            要设置的 param_name
	 */
	public void setParam_name(String param_name)
	{
		this.param_name = param_name;
	}

	/**
	 * @return param_value1
	 */
	public String getParam_value1()
	{
		return param_value1;
	}

	/**
	 * @param param_value1
	 *            要设置的 param_value1
	 */
	public void setParam_value1(String param_value1)
	{
		this.param_value1 = param_value1;
	}

	/**
	 * @return param_value2
	 */
	public String getParam_value2()
	{
		return param_value2;
	}

	/**
	 * @param param_value2
	 *            要设置的 param_value2
	 */
	public void setParam_value2(String param_value2)
	{
		this.param_value2 = param_value2;
	}

	/**
	 * @return param_value3
	 */
	public String getParam_value3()
	{
		return param_value3;
	}

	/**
	 * @param param_value3
	 *            要设置的 param_value3
	 */
	public void setParam_value3(String param_value3)
	{
		this.param_value3 = param_value3;
	}

	/**
	 * @return param_value4
	 */
	public String getParam_value4()
	{
		return param_value4;
	}

	/**
	 * @param param_value4
	 *            要设置的 param_value4
	 */
	public void setParam_value4(String param_value4)
	{
		this.param_value4 = param_value4;
	}

	/**
	 * @return param_value5
	 */
	public String getParam_value5()
	{
		return param_value5;
	}

	/**
	 * @param param_value5
	 *            要设置的 param_value5
	 */
	public void setParam_value5(String param_value5)
	{
		this.param_value5 = param_value5;
	}

	/**
	 * @return param_value6
	 */
	public String getParam_value6()
	{
		return param_value6;
	}

	/**
	 * @param param_value6
	 *            要设置的 param_value6
	 */
	public void setParam_value6(String param_value6)
	{
		this.param_value6 = param_value6;
	}

	/**
	 * @return param_value7
	 */
	public String getParam_value7()
	{
		return param_value7;
	}

	/**
	 * @param param_value7
	 *            要设置的 param_value7
	 */
	public void setParam_value7(String param_value7)
	{
		this.param_value7 = param_value7;
	}

	/**
	 * @return param_value8
	 */
	public String getParam_value8()
	{
		return param_value8;
	}

	/**
	 * @param param_value8
	 *            要设置的 param_value8
	 */
	public void setParam_value8(String param_value8)
	{
		this.param_value8 = param_value8;
	}

	/**
	 * @return param_value9
	 */
	public String getParam_value9()
	{
		return param_value9;
	}

	/**
	 * @param param_value9
	 *            要设置的 param_value9
	 */
	public void setParam_value9(String param_value9)
	{
		this.param_value9 = param_value9;
	}

	/**
	 * @return param_value10
	 */
	public String getParam_value10()
	{
		return param_value10;
	}

	/**
	 * @param param_value10
	 *            要设置的 param_value10
	 */
	public void setParam_value10(String param_value10)
	{
		this.param_value10 = param_value10;
	}
}
